﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace TPCN.DAL
{
   public class MenuDAL
    {
        public static MenuObj GetByID(string MenuID)
        {
            MenuObj obj = new MenuObj();
            using (SqlCommand cmd = new SqlCommand())
            {
                cmd.CommandText = "sp_tbMenu_GetByID";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@MenuID", MenuID);
                using (SqlDataReader dr = Database.db.GetDataToDataReader(cmd))
                {
                    if (dr.HasRows)
                    {
                        dr.Read();
                        obj = obj.SetValuesFromDataReader(dr);
                    }
                    dr.Close();
                }
            }
            return obj;
        }
       
        public static string GetMenuID()
        {
            using (SqlCommand cmd = new SqlCommand())
            {
                cmd.CommandText = "sp_tbMenu_GetId";
                cmd.CommandType = CommandType.StoredProcedure;
                SqlParameter p = new SqlParameter();
                p.DbType = DbType.Int32;
                p.Direction = ParameterDirection.Output;
                p.ParameterName = "@mID";
                cmd.Parameters.Add(p);
                Database.db.ExecuteNonQuery(cmd);
                int identity = Int32.Parse(cmd.Parameters["@mID"].Value.ToString());
                identity++;
                return identity.ToString();
            }
        }

        public static int GetNumberOfChildMenu(string ParentID, string position)
        {
            int count = 0;
            using (SqlCommand cmd = new SqlCommand())
            {
                cmd.CommandText = "sp_tbMenu_GetNumberOfChildMenu";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@ParentID", ParentID);
                cmd.Parameters.AddWithValue("@Position", position);
                SqlParameter p = new SqlParameter();
                p.DbType = DbType.Int32;
                p.Direction = ParameterDirection.Output;
                p.ParameterName = "@Count";
                cmd.Parameters.Add(p);
                Database.db.ExecuteNonQuery(cmd);
                count = Int32.Parse(cmd.Parameters["@Count"].Value.ToString());
            }
            return count;
        }

        public static List<MenuObj> GetByPosition(string position, string parentID)
        {
            List<MenuObj> result = new List<MenuObj>();
            MenuObj obj = new MenuObj();
            using (SqlCommand cmd = new SqlCommand())
            {
                cmd.CommandText = "sp_tbMenu_GetByPosition";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@Position", position);
                cmd.Parameters.AddWithValue("@ParentID", parentID);
                using (SqlDataReader dr = Database.db.GetDataToDataReader(cmd))
                {
                    if (dr.HasRows)
                    {
                        while (dr.Read())
                        {
                            result.Add(obj.SetValuesFromDataReader(dr));
                        }
                    }
                    dr.Close();
                    obj = null;
                }
            }
            return result;
        }

        public static List<MenuObj> GetRootMenu(string position)
        {
            List<MenuObj> result = new List<MenuObj>();
            MenuObj obj = new MenuObj();
            using (SqlCommand cmd = new SqlCommand())
            {
                cmd.CommandText = "sp_tbMenu_GetRootMenu";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@Position", position);
                using (SqlDataReader dr = Database.db.GetDataToDataReader(cmd))
                {
                    if (dr.HasRows)
                    {
                        while (dr.Read())
                        {
                            result.Add(obj.SetValuesFromDataReader(dr));
                        }
                    }
                    dr.Close();
                    obj = null;
                }
            }
            return result;
        }

        public static List<MenuObj> GetByAll()
        {
            List<MenuObj> result = new List<MenuObj>();
            MenuObj obj = new MenuObj();
            using (SqlCommand cmd = new SqlCommand())
            {
                cmd.CommandText = "sp_tbMenu_GetByAll";
                cmd.CommandType = CommandType.StoredProcedure;
                using (SqlDataReader dr = Database.db.GetDataToDataReader(cmd))
                {
                    if (dr.HasRows)
                    {
                        while (dr.Read())
                        {
                            result.Add(obj.SetValuesFromDataReader(dr));
                        }
                    }
                    dr.Close();
                    obj = null;
                }
            }
            return result;
        }

        public static bool Insert(MenuObj obj)
        {
            try
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.CommandText = "sp_tbMenu_Insert";
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@Name", (obj.Name == null) ? String.Empty : obj.Name);
                    cmd.Parameters.AddWithValue("@Url", (obj.Url == null) ? String.Empty : obj.Url);
                    cmd.Parameters.AddWithValue("@Keyword", (obj.Keyword == null) ? String.Empty : obj.Keyword);
                    cmd.Parameters.AddWithValue("@Description", (obj.Description == null) ? String.Empty : obj.Description);
                    cmd.Parameters.AddWithValue("@Title", (obj.Title == null) ? String.Empty : obj.Title);
                    cmd.Parameters.AddWithValue("@Type", (obj.Type == null) ? "0" : obj.Type);
                    cmd.Parameters.AddWithValue("@Content", (obj.Content == null) ? String.Empty : obj.Content);
                    cmd.Parameters.AddWithValue("@Link", (obj.Link == null) ? String.Empty : obj.Link);
                    cmd.Parameters.AddWithValue("@Position", (obj.Position == null) ? "0" : obj.Position);
                    cmd.Parameters.AddWithValue("@Order", (obj.Order == null) ? "0" : obj.Order);
                    cmd.Parameters.AddWithValue("@Active", (obj.Active == null) ? String.Empty : obj.Active);
                    cmd.Parameters.AddWithValue("@Target", (obj.Target == null) ? String.Empty : obj.Target);
                    cmd.Parameters.AddWithValue("@ParentID", (obj.ParentID == null) ? String.Empty : obj.ParentID);
                    Database.db.ExecuteNonQuery(cmd);
                }
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }

        public static bool Update(MenuObj obj)
        {
            try
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.CommandText = "sp_tbMenu_Update";
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@MenuID", (obj.MenuID == null) ? String.Empty : obj.MenuID);
                    cmd.Parameters.AddWithValue("@Name", (obj.Name == null) ? String.Empty : obj.Name);
                    cmd.Parameters.AddWithValue("@Url", (obj.Url == null) ? String.Empty : obj.Url);
                    cmd.Parameters.AddWithValue("@Keyword", (obj.Keyword == null) ? String.Empty : obj.Keyword);
                    cmd.Parameters.AddWithValue("@Description", (obj.Description == null) ? String.Empty : obj.Description);
                    cmd.Parameters.AddWithValue("@Title", (obj.Title == null) ? String.Empty : obj.Title);
                    cmd.Parameters.AddWithValue("@Type", (obj.Type == null) ? "0" : obj.Type);
                    cmd.Parameters.AddWithValue("@Content", (obj.Content == null) ? String.Empty : obj.Content);
                    cmd.Parameters.AddWithValue("@Link", (obj.Link == null) ? String.Empty : obj.Link);
                    cmd.Parameters.AddWithValue("@Position", (obj.Position == null) ? "0" : obj.Position);
                    cmd.Parameters.AddWithValue("@Order", (obj.Order == null) ? "0" : obj.Order);
                    cmd.Parameters.AddWithValue("@Active", (obj.Active == null) ? "False" : obj.Active);
                    cmd.Parameters.AddWithValue("@Target", (obj.Target == null) ? String.Empty : obj.Target);
                    cmd.Parameters.AddWithValue("@ParentID", (obj.ParentID == null) ? String.Empty : obj.ParentID);
                    Database.db.ExecuteNonQuery(cmd);
                }
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }

        public static bool Delete(string MenuID)
        {
            try
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = "sp_tbMenu_DeleteByID";
                    cmd.Parameters.AddWithValue("@MenuID", MenuID);
                    Database.db.ExecuteNonQuery(cmd);
                }
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }
    }
}
